<?php

namespace MLM\Model\Transaction;

use MLM\Model\Transaction;

class Storage extends \MLM\Model\Generic\Db\Storage {
    

    public function updateTH($data, $where){

        $sql = $this->update()
                    ->table('trans_header')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;
    }


    public function updateTD($data, $where){

        $sql = $this->update()
                    ->table('trans_detail')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;
    }

    public function getTransType($transTypeCode = ""){
        $sql = "select * from trans_type ";

        if ($transTypeCode != ""){
            $sql .= " where trans_type_code like '%$transTypeCode%' ";
        }

        $sql .= " order by trans_type_name ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();
        
        return $result;
    }



    public function buySN($memberId, $acc1, $acc2, $price, $qty, $totalPrice, $caraBayar){

        $dataInput = array($memberId, $acc1, $acc2, $price, $qty, $totalPrice, $caraBayar);

        $stmt = $this->_db->query("CALL Trx_BuySN( ?, ?, ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;
    }

    public function buySNMember($memberId, $acc1, $agen, $price, $qty, $totalPrice, $caraBayar){

        $dataInput = array($memberId, $acc1, $agen, $price, $qty, $totalPrice, $caraBayar);

        $stmt = $this->_db->query("CALL Trx_BuySNMember( ?, ?, ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;
    }


    public function buyProduct($memberId, $acc1, $acc2, $qty, $totalPrice, $caraBayar, $memberType = ""){

        $dataInput = array($memberId, $acc1, $acc2, $qty, $totalPrice, $caraBayar, $memberType);

        $stmt = $this->_db->query("CALL Trx_BuyProduct( ?, ?, ?, ?, ?, ?, ?, @out_thid, @out_thid2, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_thid as th_id, @out_thid2 as th_id2, @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;
    }


    public function getTrackingTransaction($accNum, $transType, $date1, $date2, $start, $limit, $searchField = "", $mutation = "") {

        /*
        $sql = "select count(1) as total from (select trans_dtm
                ,sum(amount1) as amount1
                ,trans_type_name,  mutation_type,
                notes_txt,
                account_num, account_name
                from v_transaction
                where trans_status = 'COMPLETED' and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and account_num1 = '$accNum'  ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        
        $sql .= " group by trans_header_id, trans_dtm ,
                trans_type_name, mutation_type, notes_txt, account_num, account_name) x";

         *
         */

        $sql = "select count(1) as total
                from v_transaction
                where trans_status = 'COMPLETED' and 
                STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d')
                between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                and seq = 1 ";

        if ($accNum != ""){

            //$sql .= " and (account_num1 = '$accNum' or account_num2 = '$accNum' )";
            $sql .= " and account_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }


        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                
        }


        $data = array($rows, 1);



        /*
        $sql = "select trans_dtm
                ,sum(amount1) as amount1
                ,trans_type_name, mutation_type,
                notes_txt,
                account_num, account_name
                from v_transaction
                where trans_status = 'COMPLETED'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and  account_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }
        
        $sql .= " group by trans_header_id, trans_dtm ,
                trans_type_name, mutation_type, notes_txt, account_num, account_name order by trans_dtm desc ";


         *
         */


        $sql = "select trans_header_id, trans_dtm, amount1
                ,trans_type_name, mutation_type,
                notes_txt,
                account_num, account_name, trans_status 
                from v_transaction
                where trans_status = 'COMPLETED'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                and seq = 1 ";

        if ($accNum != ""){

            //$sql .= " and (account_num1 = '$accNum' or account_num2 = '$accNum' )";
            $sql .= " and account_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm desc ";


        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $kredit = "0";
                $debet = "0";
                
                if ($res['mutation_type'] == "K"){
                    $kredit = $res['amount1'];
                }else{
                    $debet = $res['amount1'];
                }

                $aksiPop = '<a data-toggle="modal" class="blue" role="button" href="#detail-form" onclick="detail(\''.$res['trans_header_id'].'\')">Detail</a>';

                array_push($dataBonus, array("Tanggal" => $res['trans_dtm'], 
                        "JenisTransaksi" => $res['trans_type_name'],
                        "NoAkun" => $res['account_num'],
                        "NamaAkun" => $res['account_name'],
                        "Kredit" => $kredit,
                        "Debet" => $debet,
                        "Status" => $res['trans_status'],
                        "Detail" => $aksiPop
                    ));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getTrackingTransactionMonthly($accNum, $transType, $month,  $start, $limit, $searchField = "", $mutation = "") {


        $sqlA = "SELECT  trans_dtm, account_name, trans_type_name,
                MAX(IF(mutation_type = 'K', amount1, NULL)) as kredit,
                MAX(IF(mutation_type = 'D', amount1, NULL)) as debet
                FROM (
                    select
                    DATE_FORMAT(trans_dtm, '%Y-%m') as trans_dtm,
                    sum(amount1) as amount1
                    ,trans_type_name, mutation_type,
                    account_name
                    from v_transaction where
                    account_num1 like 'SYS%'
                    AND trans_status = 'COMPLETED'
                    and seq = 1
                    AND DATE_FORMAT(trans_dtm, '%Y%m') = '$month' 
                    ";

        if ($accNum != ""){

            //$sql .= " and (account_num1 = '$accNum' or account_num2 = '$accNum' )";
            $sqlA .= " and account_name = '$accNum' ";
        }

        if ($searchField != ""){
            $sqlA .= " and $searchField ";
        }

        $sqlA .= " group by DATE_FORMAT(trans_dtm, '%Y-%m'), trans_type_name,
                    account_name, mutation_type
                    ) xx
                GROUP BY trans_dtm, account_name, trans_type_name 
                order by account_name, trans_type_name ";


        if ($limit != ""){
            $sql = $sqlA. " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $kredit = "0";
                $debet = "0";               

                array_push($dataBonus, array("Periode" => $res['trans_dtm'],
                        "JenisTransaksi" => $res['trans_type_name'],
                        "NamaAkun" => $res['account_name'],
                        "Kredit" => $res['kredit'],
                        "Debet" => $res['debet'],
                        "Saldo" => $res['kredit'] - $res['debet']
                    ));
        }



        $sqlB = "select count(1) as total
                from (".$sqlA.") xx ";



        $stmt = $this->_db->query($sqlB);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);


        array_push($data, $dataBonus);

        return $data;
    }


    public function getBuyTransaction($accNum, $transCode, $date1, $date2, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total from (select trans_header_id, trans_code, trans_dtm
                ,sum(amount1) as amount1
                ,FORMAT(sum(amount1),2) as amount_rp
                ,trans_type_name, mutation_type, notes_txt
                from v_transaction
                where (trans_status is null or trans_status = 'APPROVAL')
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                and xs3 <> 2 and seq = 1 
                ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }

        //if ($transType != ""){
            $sql .= " and trans_type_code in ('T003','T004') ";
        //}

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_code, trans_dtm, trans_type_name, mutation_type, notes_txt) x ";
        
        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];                
        }

        $data = array($rows);


        
        $sql = "select trans_header_id, trans_code, trans_dtm
                ,sum(amount1) as amount1
                ,FORMAT(sum(amount1),2) as amount_rp
                ,trans_type_name, mutation_type, notes_txt
                from v_transaction
                where (trans_status is null or trans_status = 'APPROVAL')
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                and xs3 <> 2 and seq = 1 
                ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }

        //if ($transType != ""){
            $sql .= " and trans_type_code in ('T003','T004') ";
        //}

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }
        
        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_code, trans_dtm, trans_type_name, mutation_type, notes_txt
                  order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBuy = array();
        
        while($result->next()){
                $res = $result->current();
                
                $aksi = "<a href='javascript: void(0)' onclick='payconfirm(".$res['trans_header_id'].")'>Konfirmasi</a>";

                array_push($dataBuy, array("Tanggal" => $res['trans_dtm'], "JenisTransaksi" => $res['trans_type_name'], "KodeTransaksi" => $res['trans_code'], "Total" => $res['amount1'], "Aksi" => $aksi));

                
        }

        
        array_push($data, $dataBuy);

        return $data;
    }


    public function getTotalBalance($key) {

        $sql = "select amount, FORMAT(amount,0) as amount2
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($key));

        return $result;
    }

    public function getTransHeader($transId){


        $sql = "select trans_header_id, trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1, account_num1
                from v_transaction v
                where trans_header_id = ? and seq=1 
                group by trans_header_id, trans_dtm, trans_code, v.account_num, xs3, account_num1";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }

    public function getTransDetailProduct($transId){

        $sql = "select suplier_name, member_name, v.xs2, ceil(amount1/xn2) as price, xn2, amount1
                from v_transaction v, supliers s, members m
                where s.suplier_id = v.xs1 and m.member_id=v.xn3
                and trans_header_id = ? and seq > 1 ";



        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        $dataConfirm = array();

        $rows = 0;
        while($result->next()){
                $res = $result->current();
                array_push($dataConfirm, array("suplier" => $res['suplier_name'],
                                             "agent" => $res['member_name'],
                                             "prodname" => $res['xs2'],
                                             "price" => $res['price'],
                                             "qty" => $res['xn2'],
                                             "total" => $res['amount1']
                                             ));
                $rows++;
        }

        $data = array($rows);

        array_push($data, $dataConfirm);

        return $data;
        
    }

    public function getTransDetail($transId){

        $sql = "select * 
                from
                v_transaction v
                left outer join bank b
                on b.bank_id = v.bank_id
                where trans_header_id = ?";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }


    public function getPriceTransDetail($transId){


        $sql = "select trans_header_id, trans_code, sum(amount1) as amount1
                from
                v_transaction v
                where
                trans_header_id = ? and seq = 1 
                group by trans_header_id, trans_code";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }


    public function getConfirmPayment($memberTypeId, $memberId, $transCode, $date1, $date2, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total from (select payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                ,sum(amount1) as amount1
                from v_transaction v, payment_confirm pc where pc.trans_header_id = v.trans_header_id and
                payment_status = 0 and seq = 1 ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($memberTypeId == 4){
            $sql .= " and (v.xn3 = 100 or v.xn3 is null)  ";
            //$sql .= " and v.xn3 is null ";
        }else{
            $sql .= " and v.xn3 = $memberId ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount) x ";


        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];                
        }

        $data = array($rows);


        $sql = "select payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                ,sum(amount1) as amount1
                from v_transaction v, payment_confirm pc where pc.trans_header_id = v.trans_header_id and
                payment_status = 0 and seq = 1 ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($memberTypeId == 4){
            //$sql .= " and v.xn3 is null ";
            $sql .= " and (v.xn3 = 100 or v.xn3 is null) ";
        }else{
            $sql .= " and v.xn3 = $memberId ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                 order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataConfirm = array();

        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='payconfirm(".$res['payment_confirm_id'].")'>Verifikasi</a>";
                
                array_push($dataConfirm, array("Tgltrans" => $res['trans_dtm'],
                                             "Tgltransfer" => $res['pay_date'],
                                             "JenisTransaksi" => $res['trans_type_name'],
                                             "KodeTransaksi" => $res['trans_code'],
                                             "Total" => $res['amount1'],
                                             "Totaltransfer" => $res['transfer_amount'],
                                             "Aksi" => $aksi));
                
        }

        array_push($data, $dataConfirm);

        return $data;
    }

    
    public function getConfirmPaymentDetail($confirmId){

        /*
        $sql = "select v.*, pc.*, af.bank_acc_no as from_acc_no,
                af.bank_acc_name as from_acc_name,
                at.bank_acc_no as to_acc_no,
                at.bank_acc_name as to_acc_name, bf.bank_name as from_bank,
                bt.bank_name as to_bank, DATE_FORMAT(pc.pay_date, '%d-%m-%Y') as pay_date2
                from
                v_transaction v, payment_confirm pc, accounts af, accounts at, bank bf, bank bt
                where
                pc.trans_header_id = v.trans_header_id and payment_confirm_id = ?
                and af.account_id = pc.from_account_id and at.account_id = to_account_id
                and bf.bank_id = af.bank_id and bt.bank_id = at.bank_id";
         *
         */

        $sql = "select v.*, pc.*, af.bank_acc_no as from_acc_no,
                af.bank_acc_name as from_acc_name,
                bf.bank_name as from_bank, to_account_id,
                DATE_FORMAT(pc.pay_date, '%d-%m-%Y') as pay_date2
                from
                v_transaction v, payment_confirm pc, accounts af, bank bf
                where
                pc.trans_header_id = v.trans_header_id and payment_confirm_id = ?
                and af.account_id = pc.from_account_id 
                and bf.bank_id = af.bank_id ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($confirmId));

        return $result;

    }

    public function updateConfirmPaymentStatus($data, $where){

        $sql = $this->update()
                    ->table('payment_confirm')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;

    }

    

    public function topup($memberId, $bankAccNo, $bank, $jmlTopUp, $tglTransfer, $catatan){

        $dataInput = array($memberId, $bankAccNo, $bank, $jmlTopUp, $tglTransfer, $catatan);

        $stmt = $this->_db->query("CALL Trx_Topup( ?, ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;
    }


    public function getTopupList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T001' and (trans_status is null or trans_status = 'APPROVAL') ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v
                where trans_type_code = 'T001' and (trans_status is null or trans_status = 'APPROVAL') ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $tglTransfer = explode(" ", $res['xd1']);
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrx" => $res['trans_dtm'], "Tgltrans" => $tglTransfer[0],
                                            "NoAkun" => $res['account_num1'], "JmlTopup" => $res['amount1'],
                                            "BankAkun" => $res['xs5'], "Catatan" => $res['xs6'], "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveTopup($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_TopupFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }

    public function transfer($memberId, $accountNum, $transfer, $toAccount, $notes){

        $dataInput = array($accountNum, $memberId, $transfer, $toAccount, $notes);

        //print_r($dataInput); die();

        $stmt = $this->_db->query("CALL Trx_Transfer( ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }

    public function withdraw($memberId, $accountNum, $tarik, $bank){

        $dataInput = array($accountNum, $memberId, $tarik, $bank);

        $stmt = $this->_db->query("CALL Trx_Withdraw( ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }

    public function getWithdrawList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T005' and v.trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *


                from v_transaction v, bank b
                where trans_type_code = 'T005' 
                and b.bank_id = v.bank_id and v.trans_status = 'APPROVAL' ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $bank = "[".$res['bank_acc_no']."] ".$res['bank_name']." ".$res['bank_acc_name'];
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'], "NoAkun" => $res['account_num'], "JmlPenarikan" => $res['amount1'], "BankAkun" => $bank, "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }
    
    public function approveWithdraw($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_WithdrawFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function getBuySNList($transCode, $date1, $date2, $start, $limit, $searchField = "", $memberType = "", $memberCode = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T003' and trans_status = 'APPROVAL'
                and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";

        if ($memberType == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and v.account_num2 = 'Q000001' ";
        }else $sql .= " and v.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v
                where trans_type_code = 'T003' and trans_status = 'APPROVAL' 
                and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d')
                between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";


        if ($memberType == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and v.account_num2 = 'Q000001' ";
        }else $sql .= " and v.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                
                if ($res['xs3'] == 1) $bayar = "Cash";
                else if ($res['xs3'] == 2) $bayar = "Tukar Point";
                else $bayar = "Transfer";

                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'], "KodeTrans" => $res['trans_code'], "NoAkun" => $res['account_num'], "JmlPembelian" => $res['xn2'], "Total" => $res['amount1'], "Pembayaran" => $bayar, "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveBuySN($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_BuySNFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }
    

    public function getBuyProductList($memberId, $transCode, $date1, $date2, $start, $limit, $searchField = "", $memberType = "", $memberCode = "") {

        $sql = "select count(1) as total
                from (select trans_header_id, trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1
                from v_transaction v
                where trans_type_code = 'T004' and v.trans_status in ('APPROVAL', 'PAY_CONFIRM') 
                and seq = 1 ";
        
        if ($memberType == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and v.account_num2 = 'Q000001' ";
        }else $sql .= " and v.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_dtm, trans_code, account_num, xs3 order by trans_dtm) x ";

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows);


        $sql = "select trans_header_id, trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1
                from v_transaction v
                where trans_type_code = 'T004' and v.trans_status in ('APPROVAL', 'PAY_CONFIRM') 
                and seq = 1 ";

        if ($memberType == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and v.account_num2 = 'Q000001' ";
        }else $sql .= " and v.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_dtm, trans_code, account_num, xs3 order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataProduct = array();
        
        while($result->next()){
                $res = $result->current();

                if ($res['xs3'] == 1) $bayar = "Cash";
                else if ($res['xs3'] == 2) $bayar = "Tukar Point";
                else $bayar = "Transfer";

                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataProduct, array("Tgltrans" => $res['trans_dtm'],
                                             "KodeTrans" => $res['trans_code'],
                                             "NoAkun" => $res['account_num'],
                                             "JmlPembelian" => $res['xn2'],
                                             "Total" => $res['amount1'],
                                             "Pembayaran" => $bayar,
                                             "Aksi" => $aksi));
               
        }

        
        array_push($data, $dataProduct);

        return $data;
    }


    public function approveBuyProduct($transId, $flag){

        $dataInput = array($transId, $flag);

        $stmt = $this->_db->query("CALL Trx_BuyProductFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
        
    }


    public function getAgentList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T002' and trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v
                where trans_type_code = 'T002' and trans_status = 'APPROVAL' ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'], "NoAkun" => $res['account_num'], "Nama" => $res['account_name'], "Alamat" => $res['xs5'], "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveAgent($transId, $memberId, $agentSN, $agentPIN, $flag){

        $dataInput = array($transId, $memberId, $agentSN, $agentPIN, $flag);


        $stmt = $this->_db->query("CALL Trx_AgentFollowup( ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function claimReward($memberId, $accNum, $bonusRewardId, $rewardId){

        $dataInput = array($memberId, $accNum, $bonusRewardId, $rewardId);


        $stmt = $this->_db->query("CALL Trx_ClaimReward( ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }

    public function getClaimRewardList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v 
                where trans_type_code = 'T007' and trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v, p_bonus_reward p
                where trans_type_code = 'T007' and trans_status = 'APPROVAL'
                and p.reward_id = v.xn1 ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'],
                                            "NoAkun" => $res['account_num'],
                                            "Nama" => $res['account_name'],
                                            "Reward" => $res['reward_name'],
                                            "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveReward($transId, $bonurRewardId, $flag){

        $dataInput = array($transId, $bonurRewardId, $flag);


        $stmt = $this->_db->query("CALL Trx_ClaimRewardFollowup( ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }

    
    public function insertDataTrans($tableName, $dataTrans){
        $insert = $this->_sql->insert()
                ->into($tableName)
                ->values($dataTrans);

        $result = $this->execute($insert);

        return $result;
    }

    public function getRewardList($memberId, $numMitra, $start, $limit, $searchField = "") {

        $data = array(8);


        $sql = "select *
                from p_bonus_reward ";


        $sql = "select * from p_bonus_reward p
                left outer join t_bonus_reward t on p.reward_id = t.reward_id ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by p.reward_id ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataReward = array();
        while($result->next()){
                $res = $result->current();

                if ($res['member_id'] != ""){
                    $claim = "";
                    $sttsReward = "Milik Anda";
                    
                    if ($res['status_withdrawal'] == "" || $res['status_withdrawal'] == 0)
                        $claim = "<a href='javascript: void(0)' onclick='claim(".$res['bonus_reward_id'].",".$res['reward_id'].")'>Cairkan</a>";
                    else if ($res['status_withdrawal'] == 1)
                        $claim = "Menunggu Approval";
                    else if ($res['status_withdrawal'] < 0)
                        $claim = "Klaim Dibatalkan";
                    else if ($res['status_withdrawal'] == 2)
                        $claim = "Sudah diklaim";


                    $sttsReward = "Milik Anda";
                }else{
                    $claim = "Cairkan";
                    $sttsReward = "Menunggu";
                }

                $sttsWD = ($res['status_withdrawal'] > 1) ? "V" : "X";

                array_push($dataReward, array("level" => $res['level'],
                                            "mitra" => $res['min_mitra'],
                                            "reward" => $res['reward_name'],
                                            "sttsreward" => $sttsReward,
                                            "klaim" => $claim,
                                            "sttswithdraw" => $sttsWD));
        }


        array_push($data, $dataReward);

        return $data;
    }


    public function getRewardSNList($memberId, $numSN, $start, $limit, $searchField = "") {

        $data = array(8);


        $sql = "select *
                from p_bonus_reward ";


        $sql = "select * from p_bonus_reward p
                left outer join t_bonus_reward t on p.reward_id = t.reward_id
                and t.member_id = $memberId ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by p.reward_id ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }


        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataReward = array();
        while($result->next()){
                $res = $result->current();

                if ($res['member_id'] != ""){
                    $claim = "";
                    $sttsReward = "Milik Anda";

                    if ($res['status_withdrawal'] == "" || $res['status_withdrawal'] == 0)
                        $claim = "<a href='javascript: void(0)' onclick='claim(".$res['bonus_reward_id'].",".$res['reward_id'].")'>Cairkan</a>";
                    else if ($res['status_withdrawal'] == 1)
                        $claim = "Menunggu Approval";
                    else if ($res['status_withdrawal'] < 0)
                        $claim = "Klaim Dibatalkan";
                    else if ($res['status_withdrawal'] == 2)
                        $claim = "Sudah diklaim";


                    $sttsReward = "Milik Anda";
                }else{
                    $claim = "Cairkan";
                    $sttsReward = "Menunggu";
                }

                $sttsWD = ($res['status_withdrawal'] > 1) ? "V" : "X";

                array_push($dataReward, array("level" => $res['level'],
                                            "mitra" => $res['min_mitra'],
                                            "reward" => $res['reward_name'],
                                            "sttsreward" => $sttsReward,
                                            "klaim" => $claim,
                                            "sttswithdraw" => $sttsWD));
        }


        array_push($data, $dataReward);

        return $data;
    }



    function saveToPersonalTrans($dataTrans){
        $this->_db->beginTransaction();

        try {
            $res = $this->_db->insert('personal_data_trans',$dataTrans);

            if (!$res) {
                $status = array(
                    'code' => '1',
                    'msg' => 'Simpan dalam daftar pribadi gagal.'
                );

                $this->_db->rollBack();
                return $status;
            }

            $status = array(
                'code' => '0',
                'msg' => 'Transfer Sukses.'
            );

            $this->_db->commit();

            return $status;

        }catch (Exception $e) {
            $this->_db->rollBack();

            $status = array(
                'code' => '1',
                'msg' => 'Simpan dalam daftar pribadi gagal. '.$e
            );


            return $status;
        }
    }
    
}
